﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text;
using System.IO;
using DBScript.Utils;
using System.Data.SQLite;
using System.Configuration;

namespace DBScript
{
    /// <summary>
    /// Copyright (C) 2009-2012 Xuzhibin
    /// 数据访问适配器(基于SQLite)
    /// 用户可以修改满足自己项目的需要。
    /// Created On 2010-02-14
    /// </summary> 
    public class SQLiteProvider : ProviderBase
    {

        #region 构造函数
        public SQLiteProvider(DBScriptConfig dsConfig, bool distributed, bool rw_separate)
            : base(dsConfig, distributed, rw_separate)
        {
            LastInsert_ID_SQL = "select last_insert_rowid() from {0}";
            QueryBuilder = new SQLiteQuery();
            Params = new List<DBParam>();
        }
        #endregion

        #region 数据库链接管理
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="qty"></param>
        /// <returns></returns>
        private SQLiteConnection GetSQLiteConnection(QueryType qty)
        {
            if (IsSwitch && !string.IsNullOrEmpty(CurConnName))
            {
                return new SQLiteConnection(DBConfigs[CurConnName].ConnectionString);
            }
            string cname = GetConnName(qty);
            return new SQLiteConnection(DBConfigs[cname].ConnectionString);

        }

        #endregion 数据库链接管理

        #region Select
        /// <summary>
        /// 分页
        /// </summary>
        public override IDBBase Page(int index, int size)
        {
            int first = (index - 1) * size + 1;
            int lengh = size;
            return LimitFirst(first).LimitLengh(lengh);
        }
        #endregion Select

        #region  GetReader

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <returns></returns>
        public override IDataReader GetReader()
        {
            string strSql = QueryBuilder.ExportList();
            this.BeginTime = DateTime.Now;
            SQLiteConnection connection = GetSQLiteConnection(QueryType.Read);
            SQLiteCommand cmd = new SQLiteCommand(strSql, connection);
            try
            {
                connection.Open();

                PrepareCommand(connection, cmd, strSql, Params);
                this.QueryStr = cmd.CommandText;

                IDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                SqlDebug(string.Format("\r\nSqlOperate:{0}\r\nTable:{1}\r\nConn:{2}\r\nCommandText:\r\n{3}\r\nParameters:\r\n{4}RunTime:{5}毫秒\r\n",
                  QueryType.Read.ToString(), QueryBuilder.Table, string.Format("{0}@{1}", GetConnName(QueryType.Read), connection.DataSource), this.QueryStr, ParamsDebug(Params), RunTime()));
                
                return dr;
            }
            catch (Exception ex)
            {
                SqlDebug(string.Format("\r\nSqlOperate:{0}\r\nTable:{1}\r\nConn:{2}\r\nException:\r\n{3}\r\nRunTime:{4}毫秒\r\n",
                        QueryType.Read.ToString(), QueryBuilder.Table, string.Format("{0}@{1}", GetConnName(QueryType.Read), connection.DataSource), ex.ToString(), RunTime()));
            }
            finally
            {
                ReSet();
                //connection.Close();
            }
            return null;

        }

        #endregion  GetReader

        #region 执行SQL语句
        /// <summary>
        /// 执行sql语句并返回单个结果
        /// </summary>
        /// <param name="qty"></param>
        /// <returns></returns>
        public override object ExecuteScalar(QueryType qty)
        {
            string sqlstr = QueryBuilder.ExportSql();
            //Console.WriteLine(sqlstr);

            //事务开启
            if (IsBeginTran && qty != QueryType.Read)
            {
                TranList.Add(new TransactionItem() { TranSQL = sqlstr, TranParams = Params });
                ReSet();
                return null;
            }

            this.BeginTime = DateTime.Now;

            SQLiteConnection connection = GetSQLiteConnection(qty);
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                PrepareCommand(connection, cmd, sqlstr,Params);
                this.QueryStr = cmd.CommandText;

                object obj = cmd.ExecuteScalar();

                SqlDebug(string.Format("\r\nSqlOperate:{0}\r\nTable:{1}\r\nConn:{2}\r\nCommandText:\r\n{3}\r\nParameters:\r\n{4}RunTime:{5}毫秒\r\n",
                    qty.ToString(), QueryBuilder.Table, string.Format("{0}@{1}", GetConnName(qty), connection.DataSource), this.QueryStr, ParamsDebug(Params), RunTime()));
                ReSet();
                connection.Close();
                return obj;
            }
        }
        /// <summary>
        /// 执行sql语句，返回受影响行数
        /// </summary>
        /// <param name="sqlstr"></param>
        public override int ExecuteNonQuery(QueryType qty)
        {
            string sqlstr = QueryBuilder.ExportSql();

            //事务开启
            if (IsBeginTran && qty != QueryType.Read)
            {
                TranList.Add(new TransactionItem() { TranSQL = sqlstr, TranParams = Params }); ;
                ReSet();
                return 0;
            }

            this.BeginTime = DateTime.Now;

            SQLiteConnection connection = GetSQLiteConnection(qty);
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                PrepareCommand(connection, cmd, sqlstr, Params);

                this.QueryStr = cmd.CommandText;

                int num = cmd.ExecuteNonQuery();
                SqlDebug(string.Format("\r\nSqlOperate:{0}\r\nTable:{1}\r\nConn:{2}\r\nCommandText:\r\n{3}\r\nParameters:\r\n{4}RunTime:{5}毫秒\r\n",
                   qty.ToString(), QueryBuilder.Table, string.Format("{0}@{1}", GetConnName(qty), connection.DataSource), this.QueryStr, ParamsDebug(Params), RunTime()));
                ReSet();
                connection.Close();
                return num;
            }
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务，返回受影响行数。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public override int ExecuteSqlTran(List<TransactionItem> tranList)
        {
            int count = 0;
            this.BeginTime = DateTime.Now;
            SQLiteConnection connection = GetSQLiteConnection(QueryType.Other);
            connection.Open();
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = connection;
            SQLiteTransaction tx = connection.BeginTransaction();
            cmd.Transaction = tx;

            SqlDebug(string.Format("\r\nSqlOperate:{0}\r\nStartTime:{1}\r\nConn:{2}\r\n", "事务开始", DateTime.Now, string.Format("{0}@{1}", GetConnName(QueryType.Transaction), connection.DataSource)));

            try
            {
                foreach (TransactionItem myDE in tranList)
                {
                    string strsql = myDE.TranSQL;
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        this.QueryStr = cmd.CommandText;
                        List<DBParam> cmdParms = myDE.TranParams;
                        PrepareCommand(connection, cmd, null, cmdParms);

                        count += cmd.ExecuteNonQuery();
                        SqlDebug(string.Format("\r\nSqlOperate:{0}\r\nTable:{1}\r\nConn:{2}\r\nCommandText:\r\n{3}\r\nParameters:\r\n{4}RunTime:{5}毫秒\r\n",
                  QueryType.Transaction.ToString(), QueryBuilder.Table, string.Format("{0}@{1}", GetConnName(QueryType.Transaction), connection.DataSource), this.QueryStr, ParamsDebug(cmdParms), RunTime()));
                    }
                }
                tx.Commit();
            }
            catch (Exception ex)
            {
                SqlDebug(string.Format("\r\nSqlOperate:{0}\r\nConn:{1}\r\nCommandText:\r\n{2}\r\nStartTime:{3}\r\n",
                        "数据回滚", string.Format("{0}@{1}", GetConnName(QueryType.Transaction), connection.DataSource), ex.ToString(), DateTime.Now));
                tx.Rollback();
            }
            finally
            {
                connection.Close();
            }

            SqlDebug(string.Format("\r\nSqlOperate:{0}\r\nStartTime:{1}\r\nConn:{2}\r\n", "事务结束", DateTime.Now, string.Format("{0}@{1}", GetConnName(QueryType.Transaction), connection.DataSource)));
            return count;
        }
        #endregion

        #region 扩展方法
        private void PrepareCommand(SQLiteConnection conn, SQLiteCommand cmd, string cmdText, List<DBParam> cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            if (!string.IsNullOrEmpty(cmdText))
            {
                cmd.CommandText = cmdText;
                cmd.CommandType = CommandType.Text;//cmdType;
            }
            if (cmdParms != null)
            {
                foreach (DBParam parameter in cmdParms)
                {
                    cmd.Parameters.Add(new SQLiteParameter("$"+parameter.ParameterName, parameter.Value));
                }
            }
        }
        #endregion

        #region 辅助函数
        protected override object ConvertValue(object obj)
        {
            Type type = obj.GetType();
            switch (type.Name)
            {
                //case "UInt64":
                //    return Convert.ToBoolean(obj);
                case "Int64":
                    return Convert.ToInt32(obj);
                default:
                    break;
            }

            return base.ConvertValue(obj);
        }

        protected override void ReSet()
        {
            QueryBuilder = new SQLiteQuery();
            Params = new List<DBParam>();
            QueryStr = "";
        }
        #endregion 辅助函数
    }
}
